package com.javaweb.dao.impl;

import com.javaweb.dao.ArticleDao;
import com.javaweb.model.Article;
import com.javaweb.model.Tag;
import com.javaweb.service.dto.ArticleConditionDto;
import com.javaweb.service.dto.ArticleDto;
import com.javaweb.service.dto.ArticleUpdateDto;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.ObjectUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * @desc:
 * @author: Ping
 * @date: 2024/10/9 9:51
 */

@Repository
public class ArticleDaoImpl implements ArticleDao {

    @Autowired
    JdbcTemplate jdbcTemplate;

    @Override
    public int updateStatus(Integer id, Integer status) {
        String sql = "UPDATE tb_article SET status = ? WHERE id=?";
        return jdbcTemplate.update(sql, new Object[]{status, id});
    }


    @Override
    public int insert(Article article) {
        // Assert.notNull(article)
        if (article.getUserId() == null || article.getTitle() == null || article.getContent() == null || article.getCategoryId() == null) {
            return 0;
        }
        String sql = "INSERT INTO tb_article(user_id, title, content, status, " +
                "description, category_id, create_time, update_time)" +
                " VALUES (?,?,?,?,?,?,?,?)";
        Object[] args = new Object[]{
                article.getUserId(),
                article.getTitle(),
                article.getContent(),
                article.getStatus(),
                article.getDescription(),
                article.getCategoryId(),
                new java.sql.Date(article.getCreateTime().getTime()),
                new java.sql.Date(article.getUpdateTime().getTime())};
        int ret = jdbcTemplate.update(sql, args);
        System.out.println(ret);
        return ret > 0 ? 1 : 0;
    }

    @Override
    public int updateContent(Integer id, String newContent) {
        if (id == null || newContent == null) {
            return 0;
        }
        String sql = "update tb_article set content=?,update_time=NOW() where id=?";
        int ret = jdbcTemplate.update(sql, newContent, id);
        return ret > 0 ? 1 : 0;
    }

    @Override
    public int updateTitle(Integer id, String newTitle) {
        if (id == null || newTitle == null) {
            return 0;
        }
        String sql = "update tb_article set title=?,update_time=NOW() where id=?";
        int ret = jdbcTemplate.update(sql, newTitle, id);
        return ret > 0 ? 1 : 0;
    }

    @Override
    public int deleteById(Integer id) {
        // 检查 id 是否为空
        if (id == null) {
            return 0; // 如果 id 为空，返回 0，表示删除失败
        }

        String sql = "DELETE FROM tb_article WHERE id = ?";
        // 执行删除操作，并返回受影响的行数
        int ret = jdbcTemplate.update(sql, id);
        return ret > 0 ? 1 : 0;
    }

    @Override
    public List<Article> selectByUserId(Integer userId) {
        if (userId == null) {
            return null;
        }
        String sql = "SELECT a.*, " +
                " t.id as tag_id, t.name as tag_name, t.create_time as tag_create_time, t.update_time as tag_update_time, " +
                " c.sup, c.opp " +
                " FROM tb_article a " +
                " LEFT JOIN tb_article_tag at ON a.id=at.article_id " +
                " LEFT JOIN tb_tag t ON t.id=at.tag_id " +
                " LEFT JOIN " +
                " ( SELECT article_id, " +
                " IFNULL(SUM(support), 0) AS sup, " +
                " IFNULL(SUM(oppose),0) AS opp " +
                " FROM tb_comment GROUP BY article_id) c " +
                " ON c.article_id=a.id " +
                " WHERE a.user_id = ?";

        List<Article> articles = jdbcTemplate.query(sql, new Object[]{userId},
                rs -> {
                    Map<Integer, Article> articleMap = new HashMap<>();
                    while (rs.next()) {
                        Integer articleId = rs.getInt("id");
                        Article article = articleMap.get(articleId);
                        //为了最终的返回结果中每一个文章id只生成数据表中一行，避免在数据表中生成同样的文章id，仅仅是文章的标签不通过
                        if (ObjectUtils.isEmpty(article)) {
                            article = new Article();
                            article.setId(articleId);
                            article.setTitle(rs.getString("title"));
                            article.setContent(rs.getString("content"));
                            article.setCreateTime(rs.getDate("create_time"));
                            article.setUpdateTime(rs.getDate("update_time"));
                            article.setTags(new ArrayList<>());
                            articleMap.put(articleId, article);
                        }

                        //接下来生成标签
                        Integer tagId = rs.getInt("tag_id");
                        if (tagId != null) {
                            Tag tag = new Tag();
                            tag.setId(tagId);
                            tag.setName(rs.getString("tag_name"));
                            tag.setCreateTime(rs.getDate("tag_create_time"));
                            tag.setUpdateTime(rs.getDate("tag_update_time"));
                            article.getTags().add(tag);//同一篇文章多个标签
                        }
                        article.setSupportCount(rs.getInt("sup"));
                        article.setOpposeCount(rs.getInt("opp"));
                    }
                    return new ArrayList<>(articleMap.values());//将map转化为list集合封装
                });
        return articles.isEmpty() ? null : articles;
    }


    @Override
    public int selectId(Integer userId, String title, Date createTime) {
        // SQL 查询语句
        String sql = "SELECT id FROM tb_article WHERE user_id = ? AND title = ? AND create_time = ?";

        // 执行查询并返回结果
        try {
            return jdbcTemplate.queryForObject(sql, new Object[]{userId, title, createTime}, Integer.class);
        } catch (EmptyResultDataAccessException e) {
            // 没有找到匹配的记录，返回 0
            return 0;
        }
    }

    @Override
    public List<Article> selectByKeyword(String keyword) {
        if (keyword == null) {
            return null;
        }
        // SQL 查询，使用 LIKE 运算符来模糊匹配文章标题或内容
        String sql = "SELECT a.*, " +
                " t.id as tag_id, t.name as tag_name, t.create_time as tag_create_time, t.update_time as tag_update_time, " +
                " c.sup, c.opp " +
                " FROM tb_article a " +
                " LEFT JOIN tb_article_tag at ON a.id=at.article_id " +
                " LEFT JOIN tb_tag t ON t.id=at.tag_id " +
                " LEFT JOIN " +
                " ( SELECT article_id, " +
                " IFNULL(SUM(support), 0) AS sup, " +
                " IFNULL(SUM(oppose),0) AS opp " +
                " FROM tb_comment GROUP BY article_id) c " +
                " ON c.article_id=a.id " +
                " WHERE a.title LIKE ? OR a.content LIKE ?" +
                "ORDER BY a.create_time DESC"; // 关键字查询条件

        // 使用 jdbcTemplate 执行查询
        List<Article> articles = jdbcTemplate.query(sql, new Object[]{"%" + keyword + "%", "%" + keyword + "%"},
                rs -> {
                    Map<Integer, Article> articleMap = new HashMap<>();
                    while (rs.next()) {
                        Integer articleId = rs.getInt("id");
                        Article article = articleMap.get(articleId);

                        // 如果该文章 ID 在 Map 中不存在，创建新的 Article 对象
                        if (ObjectUtils.isEmpty(article)) {
                            article = new Article();
                            article.setId(articleId);
                            article.setTitle(rs.getString("title"));
                            article.setContent(rs.getString("content"));
                            article.setCreateTime(rs.getDate("create_time"));
                            article.setUpdateTime(rs.getDate("update_time"));
                            article.setTags(new ArrayList<>());
                            articleMap.put(articleId, article);
                        }

                        // 处理标签信息
                        Integer tagId = rs.getInt("tag_id");
                        if (tagId != null) {
                            Tag tag = new Tag();
                            tag.setId(tagId);
                            tag.setName(rs.getString("tag_name"));
                            tag.setCreateTime(rs.getDate("tag_create_time"));
                            tag.setUpdateTime(rs.getDate("tag_update_time"));
                            article.getTags().add(tag); // 将标签添加到文章中
                        }

                        // 设置支持和反对的数量
                        article.setSupportCount(rs.getInt("sup"));
                        article.setOpposeCount(rs.getInt("opp"));
                    }
                    // 将 Map 转换为 List 返回
                    return new ArrayList<>(articleMap.values());
                });
        return articles.isEmpty() ? null : articles;
    }

    @Override
    public List<Article> selectByTimePeriod(Date startTime, Date endTime) {
        // SQL 查询模板
        String sql = "SELECT a.*, " +
                " t.id as tag_id, t.name as tag_name, t.create_time as tag_create_time, t.update_time as tag_update_time, " +
                " c.sup, c.opp " +
                " FROM tb_article a " +
                " LEFT JOIN tb_article_tag at ON a.id=at.article_id " +
                " LEFT JOIN tb_tag t ON t.id=at.tag_id " +
                " LEFT JOIN " +
                " ( SELECT article_id, " +
                " IFNULL(SUM(support), 0) AS sup, " +
                " IFNULL(SUM(oppose),0) AS opp " +
                " FROM tb_comment GROUP BY article_id) c " +
                " ON c.article_id=a.id " +
                " WHERE 1=1";
        // 用于存放动态参数
        List<Object> params = new ArrayList<>();
        // 根据 startTime 和 endTime 动态添加条件
        if (startTime != null) {
            sql += " AND a.create_time >= ?";
            params.add(startTime);
        }
        if (endTime != null) {
            sql += " AND a.create_time <= ?";
            params.add(endTime);
        }
        // 添加排序条件
        sql += " ORDER BY a.create_time DESC";

        // 执行查询
        List<Article> articles = jdbcTemplate.query(sql, params.toArray(),
                rs -> {
                    Map<Integer, Article> articleMap = new HashMap<>();
                    while (rs.next()) {
                        Integer articleId = rs.getInt("id");
                        Article article = articleMap.get(articleId);

                        // 如果该文章 ID 在 Map 中不存在，创建新的 Article 对象
                        if (ObjectUtils.isEmpty(article)) {
                            article = new Article();
                            article.setId(articleId);
                            article.setTitle(rs.getString("title"));
                            article.setContent(rs.getString("content"));
                            article.setCreateTime(rs.getDate("create_time"));
                            article.setUpdateTime(rs.getDate("update_time"));
                            article.setTags(new ArrayList<>());
                            articleMap.put(articleId, article);
                        }

                        // 处理标签信息
                        Integer tagId = rs.getInt("tag_id");
                        if (tagId != null) {
                            Tag tag = new Tag();
                            tag.setId(tagId);
                            tag.setName(rs.getString("tag_name"));
                            tag.setCreateTime(rs.getDate("tag_create_time"));
                            tag.setUpdateTime(rs.getDate("tag_update_time"));
                            article.getTags().add(tag); // 将标签添加到文章中
                        }

                        // 设置支持和反对的数量
                        article.setSupportCount(rs.getInt("sup"));
                        article.setOpposeCount(rs.getInt("opp"));
                    }
                    // 将 Map 转换为 List 返回
                    return new ArrayList<>(articleMap.values());
                });
        // 如果没有结果，返回 null；否则返回文章列表
        return articles.isEmpty() ? null : articles;
    }

    @Override
    public List<Article> selectByTag(Integer tagId) {
        if (tagId == null) {
            return null;
        }
        // SQL 查询语句
        String sql = "SELECT a.*, " +
                " t.id as tag_id, t.name as tag_name, t.create_time as tag_create_time, t.update_time as tag_update_time, " +
                " c.sup, c.opp " +
                " FROM tb_article a " +
                " LEFT JOIN tb_article_tag at ON a.id = at.article_id " +
                " LEFT JOIN tb_tag t ON t.id = at.tag_id " +
                " LEFT JOIN " +
                " ( SELECT article_id, " +
                " IFNULL(SUM(support), 0) AS sup, " +
                " IFNULL(SUM(oppose), 0) AS opp " +
                " FROM tb_comment GROUP BY article_id) c " +
                " ON c.article_id = a.id " +
                " WHERE at.tag_id = ?"; // 根据标签 ID 进行过滤

        // 执行查询
        List<Article> articles = jdbcTemplate.query(sql, new Object[]{tagId},
                rs -> {
                    Map<Integer, Article> articleMap = new HashMap<>();
                    while (rs.next()) {
                        Integer articleId = rs.getInt("id");
                        Article article = articleMap.get(articleId);

                        // 如果该文章 ID 在 Map 中不存在，创建新的 Article 对象
                        if (ObjectUtils.isEmpty(article)) {
                            article = new Article();
                            article.setId(articleId);
                            article.setTitle(rs.getString("title"));
                            article.setContent(rs.getString("content"));
                            article.setCreateTime(rs.getDate("create_time"));
                            article.setUpdateTime(rs.getDate("update_time"));
                            article.setTags(new ArrayList<>());
                            articleMap.put(articleId, article);
                        }

                        // 处理标签信息
                        Integer tagIdFromResultSet = rs.getInt("tag_id");
                        if (tagIdFromResultSet != null) {
                            Tag tag = new Tag();
                            tag.setId(tagIdFromResultSet);
                            tag.setName(rs.getString("tag_name"));
                            tag.setCreateTime(rs.getDate("tag_create_time"));
                            tag.setUpdateTime(rs.getDate("tag_update_time"));
                            article.getTags().add(tag); // 将标签添加到文章中
                        }

                        // 设置支持和反对的数量
                        article.setSupportCount(rs.getInt("sup"));
                        article.setOpposeCount(rs.getInt("opp"));
                    }
                    // 将 Map 转换为 List 返回
                    return new ArrayList<>(articleMap.values());
                });

        // 返回文章列表，若无结果则返回 null
        return articles.isEmpty() ? null : articles;
    }

    @Override
    public List<Article> selectByCategory(Integer categoryId) {
        if (categoryId == null) {
            return null;
        }
        // SQL 查询语句
        String sql = "SELECT a.*, " +
                " t.id as tag_id, t.name as tag_name, t.create_time as tag_create_time, t.update_time as tag_update_time, " +
                " c.sup, c.opp " +
                " FROM tb_article a " +
                " LEFT JOIN tb_article_tag at ON a.id=at.article_id " +
                " LEFT JOIN tb_tag t ON t.id=at.tag_id " +
                " LEFT JOIN " +
                " ( SELECT article_id, " +
                " IFNULL(SUM(support), 0) AS sup, " +
                " IFNULL(SUM(oppose), 0) AS opp " +
                " FROM tb_comment GROUP BY article_id) c " +
                " ON c.article_id=a.id " +
                " WHERE a.category_id = ?"; // 根据分类 ID 进行过滤

        // 执行查询
        List<Article> articles = jdbcTemplate.query(sql, new Object[]{categoryId},
                rs -> {
                    Map<Integer, Article> articleMap = new HashMap<>();
                    while (rs.next()) {
                        Integer articleId = rs.getInt("id");
                        Article article = articleMap.get(articleId);

                        // 如果该文章 ID 在 Map 中不存在，创建新的 Article 对象
                        if (ObjectUtils.isEmpty(article)) {
                            article = new Article();
                            article.setId(articleId);
                            article.setTitle(rs.getString("title"));
                            article.setContent(rs.getString("content"));
                            article.setCreateTime(rs.getDate("create_time"));
                            article.setUpdateTime(rs.getDate("update_time"));
                            article.setTags(new ArrayList<>());
                            articleMap.put(articleId, article);
                        }

                        // 处理标签信息
                        Integer tagId = rs.getInt("tag_id");
                        if (tagId != null) {
                            Tag tag = new Tag();
                            tag.setId(tagId);
                            tag.setName(rs.getString("tag_name"));
                            tag.setCreateTime(rs.getDate("tag_create_time"));
                            tag.setUpdateTime(rs.getDate("tag_update_time"));
                            article.getTags().add(tag); // 将标签添加到文章中
                        }

                        // 设置支持和反对的数量
                        article.setSupportCount(rs.getInt("sup"));
                        article.setOpposeCount(rs.getInt("opp"));
                    }
                    // 将 Map 转换为 List 返回
                    return new ArrayList<>(articleMap.values());
                });

        // 返回文章列表
        return articles.isEmpty() ? null : articles;
    }

    @Override
    public Article selectById(String articleId) {
        String sql = "SELECT * FROM tb_article WHERE id = ?";
        return jdbcTemplate.queryForObject(sql, new Object[]{articleId}, new BeanPropertyRowMapper<>(Article.class));
    }

    @Override
    public int countByConditions(ArticleConditionDto articleCond) {
        String sql = "SELECT COUNT(*) FROM tb_article WHERE 1=1";
        List<Object> params = new ArrayList<>();

        if (articleCond.getKeyword() != null) {
            sql += " AND title LIKE ?";
            params.add("%" + articleCond.getKeyword() + "%");
        }
        if (articleCond.getStatus() != null) {
            sql += " AND status = ?";
            params.add(articleCond.getStatus());
        }
        return jdbcTemplate.queryForObject(sql, params.toArray(), Integer.class);

    }

    @Override
    public List<Article> selectByConditions(ArticleConditionDto articleCond, int fromIndex, int pageSize) {
        String sql = "SELECT * FROM tb_article WHERE 1=1";
        List<Object> params = new ArrayList<>();

        if (articleCond.getKeyword() != null) {
            sql += " AND title LIKE ?";
            params.add("%" + articleCond.getKeyword() + "%");
        }
        if (articleCond.getStatus() != null) {
            sql += " AND status = ?";
            params.add(articleCond.getStatus());
        }
        sql += " LIMIT ?, ?"; // 添加分页
        params.add(fromIndex);
        params.add(pageSize);

        return jdbcTemplate.query(sql, params.toArray(), new BeanPropertyRowMapper<>(Article.class));
    }

    @Override
    public int updatearticle(Article article) {
        String sql = "UPDATE tb_article SET title=?, content=?, description=?, category_id=?, status=? WHERE id=?";

        return jdbcTemplate.update(sql, article.getTitle(), article.getContent(),
                article.getDescription(), article.getCategoryId(), article.getStatus(), article.getId());
    }

    @Override
    public List<Article> findArticlesByConditions(ArticleConditionDto articleCond) {
        StringBuilder sql = new StringBuilder("SELECT a.* FROM tb_article a");
        List<Object> params = new ArrayList<>();

        // 如果提供了标签，使用JOIN查询
        if (articleCond.getTags() != null && !articleCond.getTags().isEmpty()) {
            sql.append(" JOIN tb_article_tag at ON a.id = at.article_id");
            sql.append(" JOIN tb_tag t ON at.tag_id = t.id");
        }

        sql.append(" WHERE 1=1");

        // 根据条件动态拼接 SQL
        if (articleCond.getId() != null) {
            sql.append(" AND a.id = ?");
            params.add(articleCond.getId());
        }
        if (articleCond.getKeyword() != null && !articleCond.getKeyword().isEmpty()) {
            sql.append(" AND a.title LIKE ?");
            params.add("%" + articleCond.getKeyword() + "%");
        }
        if (articleCond.getCategoryName() != null) {
            sql.append(" AND a.category_id = (SELECT id FROM tb_category WHERE name = ?)");
            params.add(articleCond.getCategoryName());
        }

        // 处理标签过滤
        if (articleCond.getTags() != null && !articleCond.getTags().isEmpty()) {
            String[] tags = articleCond.getTags().split(",");
            sql.append(" AND t.name IN (");
            for (int i = 0; i < tags.length; i++) {
                sql.append("?");
                if (i < tags.length - 1) {
                    sql.append(", ");
                }
                params.add(tags[i].trim());
            }
            sql.append(")");
        }

        if (articleCond.getStatus() != null) {
            sql.append(" AND a.status = ?");
            params.add(articleCond.getStatus());
        }
        if (articleCond.getStartCreateTime() != null) {
            sql.append(" AND a.create_time >= ?");
            params.add(articleCond.getStartCreateTime());
        }
        if (articleCond.getEndCreateTime() != null) {
            sql.append(" AND a.create_time <= ?");
            params.add(articleCond.getEndCreateTime());
        }

        // 执行查询
        return jdbcTemplate.query(sql.toString(), params.toArray(), (rs, rowNum) -> {
            Article article = new Article();
            article.setId(rs.getInt("id"));
            article.setUserId(rs.getInt("user_id"));
            article.setTitle(rs.getString("title"));
            article.setContent(rs.getString("content"));
            article.setStatus(rs.getInt("status"));
            article.setDescription(rs.getString("description"));
            article.setCategoryId(rs.getInt("category_id"));
            article.setCreateTime(rs.getTimestamp("create_time"));
            article.setUpdateTime(rs.getTimestamp("update_time"));
            return article;
        });
    }

    @Override
    public void add(Article article) {
        String sql = "INSERT INTO tb_article (user_id, title, content, status," +
                " description, category_id, create_time, update_time) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        jdbcTemplate.update(sql, article.getUserId(), article.getTitle(),
                article.getContent(), article.getStatus(), article.getDescription(),
                article.getCategoryId(), article.getCreateTime(), article.getUpdateTime());
    }

    @Override
    public boolean updateArticle(ArticleUpdateDto articleUpdateDto, List<Integer> tagIds) {
        String sql = "UPDATE tb_article SET title = ?, content = ?, description = ?, " +
                "status = ?, category_id = ?, update_time = NOW() WHERE id = ?";
        int rowsAffected = jdbcTemplate.update(sql,
                articleUpdateDto.getTitle(),
                articleUpdateDto.getContent(),
                articleUpdateDto.getDescription(),
                articleUpdateDto.getStatus(),
                articleUpdateDto.getCategoryId(),
                articleUpdateDto.getId());

        // 更新成功后，更新标签信息
        if (rowsAffected > 0) {
            updateArticleTags(articleUpdateDto.getId(), tagIds);
            return true;
        } else {
            return false;
        }
    }

    @Override
    public boolean publishArticle(String articleId) {
        String sql = "UPDATE tb_article SET status = ?, update_time = NOW() WHERE id = ?";

        int rowsAffected = jdbcTemplate.update(sql, 1, articleId);

        return rowsAffected > 0;
    }

    @Override
    public void publish(String articleId) {

    }

    @Override
    public void save(ArticleDto articleDto) {

    }

    private void updateArticleTags(String articleId, List<Integer> tagIds) {
        // 删除旧的标签关系
        String deleteSql = "DELETE FROM tb_article_tag WHERE article_id = ?";
        jdbcTemplate.update(deleteSql, articleId);

        // 插入新的标签关系
        String insertSql = "INSERT INTO tb_article_tag (article_id, tag_id) VALUES (?, ?)";
        for (Integer tagId : tagIds) {
            jdbcTemplate.update(insertSql, articleId, tagId);
        }
    }
}
